package TicketingSystem;

import connection.Conn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TicketingService {
    private static final Logger LOGGER = Logger.getLogger(TicketingService.class.getName());

    // 新增方法：获取所有场地信息
    public List<Venue> getVenues() {
        List<Venue> venues = new ArrayList<>();
        // 修改SQL查询语句，获取影厅相关信息
        String sql = "SELECT v.VenueID, v.VenueName, v.Address, v.Capacity, h.HallID, h.HallName, h.HallCapacity " +
                "FROM Venues v " +
                "LEFT JOIN Halls h ON v.VenueID = h.VenueID";
        try (Connection conn = Conn.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int venueID = rs.getInt("VenueID");
                    String venueName = rs.getString("VenueName");
                    String address = rs.getString("Address");
                    int capacity = rs.getInt("Capacity");
                    int hallID = rs.getInt("HallID");
                    String hallName = rs.getString("HallName");
                    int hallCapacity = rs.getInt("HallCapacity");
                    Venue venue = new Venue();
                    venue.setVenueID(venueID);
                    venue.setVenueName(venueName);
                    venue.setAddress(address);
                    venue.setCapacity(capacity);
                    // 设置影厅相关信息
                    venue.setHallID(hallID);
                    venue.setHallName(hallName);
                    venue.setHallCapacity(hallCapacity);
                    venues.add(venue);
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "执行获取场地信息操作时出现 SQL 异常", e);
        }
        return venues;
    }

//    获取单个电影信息
    public List<Hall> getHallsByVenueId(int venueID) {
        List<Hall> halls = new ArrayList<>();
        String sql = "SELECT HallID, HallName, HallCapacity FROM Halls WHERE VenueID = ?";
        try (Connection conn = Conn.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, venueID);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int hallID = rs.getInt("HallID");
                    String hallName = rs.getString("HallName");
                    int hallCapacity = rs.getInt("HallCapacity");
                    Hall hall = new Hall(hallID, hallName, hallCapacity);
                    halls.add(hall);
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "执行获取影厅信息操作时出现 SQL 异常", e);
        }
        return halls;
    }

    // 票务查询功能
    public static List<Movie> queryMovies(String movieName) {
        List<Movie> movies = new ArrayList<>();
        String sql;
        if (movieName == null || movieName.isEmpty()) {
            sql = "SELECT * FROM Movies";
        } else {
            sql = "SELECT * FROM Movies WHERE MovieName LIKE ?";
        }
        try (Connection conn = Conn.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            if (movieName != null && !movieName.isEmpty()) {
                pstmt.setString(1, "%" + movieName + "%");
            }
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int movieID = rs.getInt("MovieID");
                    String movieNameResult = rs.getString("MovieName");
                    int releaseYear = rs.getInt("ReleaseYear");
                    String actors = rs.getString("Actors");
                    String director = rs.getString("Director");
                    String screenwriter = rs.getString("Screenwriter");
                    String plot = rs.getString("Plot");
                    Movie movie = new Movie(movieID, movieNameResult, releaseYear, actors, director, screenwriter, plot);
                    movies.add(movie);
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "执行电影查询时出现 SQL 异常", e);
        }
        return movies;
    }

    // 场地预定功能
    public boolean bookVenue(int userID, int venueID, Date startDate, Time startTime, Date endDate, Time endTime, int hallID) {
        try (Connection conn = Conn.getConn()) {
            // 检查场地是否已被预定
            String checkSql = "SELECT * FROM VenueBookings WHERE VenueID = ? AND HallID = ? AND ((StartDate <= ? AND EndDate >= ?) AND (StartTime <= ? AND EndTime >= ?))";
            try (PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
                pstmt.setInt(1, venueID);
                pstmt.setInt(2, hallID);
                pstmt.setDate(3, startDate);
                pstmt.setDate(4, startDate);
                pstmt.setTime(5, startTime);
                pstmt.setTime(6, startTime);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        LOGGER.info("该场地在指定日期和时间已被预定");
                        return false;
                    }
                }
            }

            // 插入场地预定记录
            String insertSql = "INSERT INTO VenueBookings (UserID, VenueID, BookingDate, StartDate, StartTime, EndDate, EndTime, HallID) "
                    + "VALUES (?, ?, NOW(), ?, ?, ?, ?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                pstmt.setInt(1, userID);
                pstmt.setInt(2, venueID);
                pstmt.setDate(3, startDate);
                pstmt.setTime(4, startTime);
                pstmt.setDate(5, endDate);
                pstmt.setTime(6, endTime);
                pstmt.setInt(7, hallID); // 设置新增的影厅编号
                pstmt.executeUpdate();
                return true;
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "执行场地预定操作时出现 SQL 异常", e);
            return false;
        }
    }

    public List<Order> getOrdersByUser(int userID) {
        List<Order> orders = new ArrayList<>();
        String sql = "SELECT o.OrderID, o.StartDate, o.StartTime, o.EndTime, o.TotalAmount, o.OrderStatus, " +
                "m.MovieName, v.VenueName, h.HallName " +
                "FROM Orders o " +
                "JOIN Movies m ON o.MovieID = m.MovieID " +
                "JOIN Venues v ON o.VenueID = v.VenueID " +
                "JOIN Halls h ON o.HallID = h.HallID " +
                "WHERE o.UserID = ?";
        try (Connection conn = Conn.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderID(rs.getInt("OrderID"));
                order.setStartDate(rs.getString("StartDate"));
                order.setStartTime(rs.getString("StartTime"));
                order.setEndTime(rs.getString("EndTime"));
                order.setTotalAmount(rs.getInt("TotalAmount"));
                order.setOrderStatus(rs.getString("OrderStatus"));
                order.setMovieName(rs.getString("MovieName"));
                order.setVenueName(rs.getString("VenueName"));
                order.setHallName(rs.getString("HallName"));
                orders.add(order);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return orders;
    }

//    // 获取单个订单详情
    public Order getOrderDetails(int orderID) {
        Order order = new Order();
        String sql = "SELECT o.OrderID, o.StartDate, o.StartTime, o.EndTime, o.TotalAmount, o.OrderStatus, " +
                "m.MovieName, v.VenueName, h.HallName " +
                "FROM Orders o " +
                "JOIN Movies m ON o.MovieID = m.MovieID " +
                "JOIN Venues v ON o.VenueID = v.VenueID " +
                "JOIN Halls h ON o.HallID = h.HallID " +
                "WHERE o.OrderID = ?";
        try (Connection conn = Conn.getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, orderID);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                order.setOrderID(rs.getInt("OrderID"));
                order.setStartDate(rs.getString("StartDate"));
                order.setStartTime(rs.getString("StartTime"));
                order.setEndTime(rs.getString("EndTime"));
                order.setTotalAmount(rs.getInt("TotalAmount"));
                order.setOrderStatus(rs.getString("OrderStatus"));
                order.setMovieName(rs.getString("MovieName"));
                order.setVenueName(rs.getString("VenueName"));
                order.setHallName(rs.getString("HallName"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return order;
    }
//  退款申请
    public static boolean applyForRefund(int orderID, int reasonID) {
        try (Connection conn = Conn.getConn()) {
            conn.setAutoCommit(false);
            try {
                // 1. 检查订单状态
                String checkSql = "SELECT OrderStatus, RefundStatus FROM Orders WHERE OrderID = ?";
                try (PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
                    pstmt.setInt(1, orderID);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        if (rs.next()) {
                            String orderStatus = rs.getString("OrderStatus");
                            String refundStatus = rs.getString("RefundStatus");

                            // 只允许未完成的订单申请退款
                            if (!"未完成".equals(orderStatus)/* || !"未申请".equals(refundStatus)*/) {
                                LOGGER.warning("订单不符合退款条件");
                                return false;
                            }
                        } else {
                            LOGGER.warning("订单不存在");
                            return false;
                        }
                    }
                }

                // 2. 更新订单状态
                String updateSql = "UPDATE Orders SET OrderStatus = '退款申请中', RefundStatus = '申请中', RefundReasonID = ? WHERE OrderID = ?";
                try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
                    pstmt.setInt(1, reasonID);
                    pstmt.setInt(2, orderID);

                    int affectedRows = pstmt.executeUpdate();
                    if (affectedRows == 0) {
                        conn.rollback();
                        return false;
                    }
                    conn.commit();
                    return true;
                }
            } catch (SQLException e) {
                conn.rollback();
                LOGGER.log(Level.SEVERE, "数据库操作失败", e);
                return false;
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "数据库连接失败", e);
            return false;
        }
    }

//    申请退款
    public class RefundService {
        private static final Logger logger = Logger.getLogger(RefundService.class.getName());

        public static List<RefundApplication> getRefundApplications() {
            List<RefundApplication> applications = new ArrayList<>();
            String sql = "SELECT o.OrderID, m.MovieName, v.VenueName, h.HallName, rr.Description AS RefundReason, o.RefundStatus " +
                    "FROM Orders o " +
                    "JOIN Movies m ON o.MovieID = m.MovieID " +
                    "JOIN Venues v ON o.VenueID = v.VenueID " +
                    "JOIN Halls h ON o.HallID = h.HallID " +
                    "JOIN RefundReasons rr ON o.RefundReasonID   = rr.ReasonID  " +
                    "WHERE o.RefundStatus = '申请中'";
            try (Connection conn = Conn.getConn();
                 PreparedStatement pstmt = conn.prepareStatement(sql);
                 ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int orderID = rs.getInt("OrderID");
                    String movieName = rs.getString("MovieName");
                    String venueName = rs.getString("VenueName");
                    String hallName = rs.getString("HallName");
                    String refundReason = rs.getString("RefundReason");
                    String refundStatus = rs.getString("RefundStatus");
                    applications.add(new RefundApplication(orderID, movieName, venueName, hallName, refundReason, refundStatus));
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return applications;
        }

//        通过
        public static boolean approveRefund(int orderID) {
            String sql = "UPDATE Orders SET RefundStatus = '已退款', OrderStatus = '已退款' WHERE OrderID = ?";
            try (Connection conn = Conn.getConn();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, orderID);
                return pstmt.executeUpdate() > 0;
            } catch (SQLException ex) {
                logger.log(Level.SEVERE, "通过退票申请失败", ex);
                return false;
            }
        }

//        拒绝
        public static boolean rejectRefund(int orderID) {
            String sql = "UPDATE Orders SET RefundStatus = '已拒绝', OrderStatus = '退款异常' WHERE OrderID = ?";
            try (Connection conn = Conn.getConn();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setInt(1, orderID);
                return pstmt.executeUpdate() > 0;
            } catch (SQLException ex) {
                logger.log(Level.SEVERE, "拒绝退票申请失败", ex);
                return false;
            }
        }
    }
}